from django.db.models import Max
from django.http.response import JsonResponse
from django.views.decorators.http import require_http_methods

import sys, MySQLdb

from backend.models import DataStandardIndex
from utils.http_response import *
import json

sys.path.insert(0, '..')
from mysite import db_config


def db_query(index_id, std_type):
    conn = db_config.mysql_connect()
    curs = conn.cursor()
    if std_type == 'detail':
        '''
        请求类型：GET

        请求参数：std_name数据标准名

        返回参数：
            id                      主键id
            std_id                  标准编号
            name                    标准名称
            en_name                 标准英文名称
            business_definition     业务定义
            business_rule           业务规则
            std_source              标准来源
            data_type               数据类型
            data_format             数据格式
            code_rule               编码规则
            code_range              编码范围
            code_meaning            编码含义
            business_range          业务范围
            dept                    数据责任部门
            system                  数据使用系统
        '''
        sql = f"""select id,std_id,name,en_name,business_definition,business_rule,std_source,data_type,data_format,
code_rule,code_range,code_meaning,business_range,dept,`system`
from data_standard_detail
where index_id={index_id}"""
        print(sql)
        curs.execute(sql)
        result = curs.fetchone()
        if result is not None:
            return {
                'id': result[0],
                'std_id': result[1],
                'name': result[2],
                'en_name': result[3],
                'business_definition': result[4],
                'business_rule': result[5],
                'std_source': result[6],
                'data_type': result[7],
                'data_format': result[8],
                'code_rule': result[9],
                'code_range': result[10],
                'code_meaning': result[11],
                'business_range': result[12],
                'dept': result[13],
                'system': result[14]
            }
        else:
            return {
                'id': '',
                'std_id': '',
                'name': '',
                'en_name': '',
                'business_definition': '',
                'business_rule': '',
                'std_source': '',
                'data_type': '',
                'data_format': '',
                'code_rule': '',
                'code_range': '',
                'code_meaning': '',
                'business_range': '',
                'dept': '',
                'system': ''
            }
    elif std_type == 'desc':
        '''
        请求类型：GET

        请求参数：std_name数据标准名

        返回参数：
            id          主键id
            name        标准名称
            content     标准内容    
        '''
        sql = f"select id,name,content from data_standard_desc where index_id='{index_id}'"
        curs.execute(sql)
        result = curs.fetchone()
        if result is None:
            result = ['', '', '']
        return {
            'id': result[0],
            'name': result[1],
            'content': result[2]
        }
    curs.close()
    conn.close()


# 查询数据标准
@require_http_methods(["GET"])
def query_detail(request):
    index_id = request.GET.get('index_id')
    std_type = request.GET.get('std_type')

    if not all([index_id, std_type]):
        return JsonResponse({'msg': '请求参数缺失', 'code': 3000})

    data = db_query(index_id, std_type)
    return JsonResponse(data)


# 查询数据标准编辑记录
@require_http_methods(["GET"])
def query_update_history(request):
    std_name = request.GET.get('std_name')

    if std_name is None:
        return JsonResponse({'msg': '请求参数缺失', 'code': 3000})

    conn = db_config.mysql_connect()
    curs = conn.cursor()
    sql = f"select username,update_time from data_standard_update_log where std_name='{std_name}' order by update_time desc limit 1"
    if curs.execute(sql) == 1:
        result = curs.fetchone()
        return JsonResponse({'username': result[0], 'last_update_time': str(result[1])})
    else:
        return JsonResponse({'username': None, 'last_update_time': None})


# 更新数据标准
@require_http_methods(["POST"])
def update(request):
    body_info = request.body
    return_msg = repo().success()
    try:
        if body_info is not None and len(body_info) > 0:
            params = json.loads(body_info)
            username = params.get('username')
            std_type = params.get('std_type')
            std_name = params.get('std_name')
            en_name = params.get('en_name')
            business_definition = params.get('business_definition')
            business_rule = params.get('business_rule')
            std_source = params.get('std_source')
            data_type = params.get('data_type')
            data_format = params.get('data_format')
            code_rule = params.get('code_rule')
            code_range = params.get('code_range')
            code_meaning = params.get('code_meaning')
            business_range = params.get('business_range')
            dept = params.get('dept')
            system = params.get('system')
            content = params.get('content')
            # 子id
            sub_id = params.get('sub_id')
            index_id = params.get('index_id')
            conn = db_config.mysql_connect()
            curs = conn.cursor()
            curs.execute('set autocommit=0')

            if not all([std_name, std_type]):
                return JsonResponse(repo().fail('请求参数缺失'))

            # post内容与数据库内容对比，如果内容一致则无需update

            orgin_data = db_query(index_id, std_type)

            if std_type == 'desc':
                post_data = {'name': std_name, 'content': content}
                # 如果是空  进行新增
                if orgin_data.get("id") == '':
                    add_sql = f""" insert into data_standard_desc (index_id,name,content) values (
                    {index_id},'{std_name}','{content}' )
                    """
                    curs.execute(add_sql)
                elif post_data == orgin_data:
                    return JsonResponse(repo().fail('内容一致，无需修改'))
                else:
                    # 把上一版本的数据标准内容存入到日志表
                    update_log = str(orgin_data.items() - post_data.items())  # 将被update替换的内容
                    sql = f"insert into data_standard_update_log(std_name, username, previous_version,index_id) values('{std_name}', '{username}', \"{update_log}\",{index_id})"
                    curs.execute(sql)
                    # 更新数据标准
                    sql = f"update data_standard_desc set name='{std_name}', content='{content}' where index_id='{index_id}'"
                    curs.execute(sql)
            elif std_type == 'detail':
                post_data = {
                    'name': std_name,
                    'en_name': en_name,
                    'business_definition': business_definition,
                    'business_rule': business_rule,
                    'std_source': std_source,
                    'data_type': data_type,
                    'data_format': data_format,
                    'code_rule': code_rule,
                    'code_range': code_range,
                    'code_meaning': code_meaning,
                    'business_range': business_range,
                    'dept': dept,
                    'system': system,
                }
                if orgin_data.get("id") == '':
                    add_sql = f""" insert into data_standard_detail(index_id,name,en_name,business_definition,business_rule,
                    std_source,data_type,data_format,code_rule,code_range,code_meaning,business_range,dept,system
                    )values( {index_id},'{std_name}','{en_name}','{business_definition}','{business_rule}', '{std_source}',
                    '{data_type}','{data_format}','{code_rule}','{code_range}','{code_meaning}','{business_range}',
                    '{dept}','{system}'
                    )
                    """
                    curs.execute(add_sql)
                elif post_data == orgin_data:
                    return JsonResponse(repo().fail('内容一致，无需修改'))
                else:
                    # 把上一版本的数据标准内容存入到日志表
                    update_log = str(orgin_data.items() - post_data.items())  # 将被update替换的内容
                    sql = f"insert into data_standard_update_log(std_name, username, previous_version,index_id) values('{std_name}', '{username}', \"{update_log}\",{index_id})"
                    curs.execute(sql)
                    sql = f"""update data_standard_detail set name = '{std_name}', 
                                                        en_name = '{en_name}',
                                                        business_definition = '{business_definition}', 
                                                        business_rule = '{business_rule}',
                                                        std_source = '{std_source}',
                                                        data_type = '{data_type}',
                                                        data_format = '{data_format}',
                                                        code_rule = '{code_rule}',
                                                        code_range = '{code_range}',
                                                        code_meaning = '{code_meaning}',
                                                        business_range = '{business_range}',
                                                        dept = '{dept}',
                                                        `system` = '{system}'
                        where index_id='{index_id}'"""
                    curs.execute(sql)
            conn.commit()
            curs.close()
            conn.close()
    except Exception as e:
        return_msg = repo().fail(msg=str(e))
    return JsonResponse(return_msg)


# 获取数据标准目录
@require_http_methods(["GET"])
def query_index(request):
    pk_id = request.GET.get("pk_id")
    params = {}
    if pk_id is not None:
        params["pk_id"] = pk_id
    result = DataStandardIndex.objects.all().filter(**params)
    return_result = []
    for data in result:
        return_result.append({
            'pk_id': data.pk_id,
            'id': data.idx_id,
            'pId': data.idx_pid,
            'name': data.idx_name,
            't': data.idx_name,
            'open': data.is_open,
            'is_dir': data.is_dir
        })

    return JsonResponse(repo().success(data=return_result), safe=False)


def get_base_num(idx_pid):
    base_num = 0
    if idx_pid == 0:
        base_num = 0
    else:
        base_num = 100
    # else:
    #     length = len(str(idx_pid)) + 1
    #     base_str = "1"
    #     while length > 0:
    #         base_str += "0"
    #         length = length - 1
    #
    #     base_num = int(base_str)

    return base_num


@require_http_methods(["POST"])
def add_index(request):
    body_info = request.body
    return_msg = repo().success()
    try:
        if body_info is not None and len(body_info) > 0:
            params = json.loads(body_info)
            # 先获取父id
            idx_pid = params.get("pId")
            idx_id = 0
            # 根据父id  获取子节点最大值
            filter_max = DataStandardIndex.objects.filter(idx_pid=idx_pid).aggregate(max_num=Max("idx_id"))
            # 如果存在子节点   子节点加1  否则  按照规则来
            max_num = filter_max.get("max_num")
            if max_num is not None:
                idx_id = max_num + 1
            else:
                base_num = get_base_num(idx_pid)
                idx_id = base_num * idx_pid + 1

            data_standard = DataStandardIndex()
            data_standard.idx_pid = params.get("pId")
            data_standard.idx_id = idx_id
            data_standard.is_open = params.get("open")
            data_standard.idx_name = params.get("name")
            data_standard.is_dir = params.get("is_dir")
            data_standard.save()

            return_msg.get("data").append({"pId": data_standard.idx_pid, "name": data_standard.idx_name,
                                           "open": data_standard.is_open, "pk_id": data_standard.pk_id,
                                           "is_dir": data_standard.is_dir, "id": data_standard.idx_id})
    except Exception as e:
        return_msg = repo().fail(msg=str(e))
    return JsonResponse(return_msg)


@require_http_methods(["POST"])
def delete_index(request):
    body_info = request.body
    return_msg = repo().success()
    try:
        if body_info is not None and len(body_info) > 0:
            params = json.loads(body_info)
            pk_id = params.get("pk_id")
            id = params.get("id")
            data_result = DataStandardIndex.objects.filter(idx_pid=id)
            if data_result.exists():
                raise Exception("存在子节点，不能删除！")
            # 删除目录
            DataStandardIndex.objects.filter(pk_id=pk_id).delete()
            # 同时需要删除该目录对应的数据规则
            sql_del_rule = f"""delete from data_standard_desc where index_id = {pk_id}"""
            conn = db_config.mysql_connect()
            try:
                with conn.cursor() as curs:
                    curs.execute('set autocommit=0')
                    curs.execute(sql_del_rule)
                    conn.commit()
            except Exception as e:
                print(str(e))
            finally:
                curs.close()
                conn.close()

    except Exception as e:
        return_msg = repo().fail(msg=str(e))
    return JsonResponse(return_msg)


@require_http_methods(["POST"])
def update_index(request):
    body_info = request.body
    return_msg = repo().success()
    try:
        if body_info is not None and len(body_info) > 0:
            params = json.loads(body_info)
            data_standard = DataStandardIndex.objects.get(pk_id=int(params.get("pk_id")))
            data_standard.idx_pid = params.get("pId")
            data_standard.idx_name = params.get("name")
            data_standard.is_open = params.get("open")
            data_standard.save()
            name = params.get("name")
            pk_id = params.get("pk_id")
            sql = f"update data_standard_desc set name='{name}' where index_id='{pk_id}'"
            conn = db_config.mysql_connect()
            curs = conn.cursor()
            curs.execute('set autocommit=0')
            curs.execute(sql)
            conn.commit()
    except Exception as e:
        return_msg = repo().fail(msg=str(e))
    return JsonResponse(return_msg)

